package com.sh.data.engine.domain.secret.service.impl;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.sh.data.engine.domain.dataasset.service.TblPropService;
import com.sh.data.engine.domain.secret.service.SecretMgtService;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.drop.Drop;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.truncate.Truncate;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author: mengzheng.mhc
 * @date: 2024/7/9 18:04
 */
@Service
public class SecretMgtServiceImpl implements SecretMgtService {

    @Lazy
    @Autowired
    private TblPropService tblPropService;

    @Override
    public Map<String, List<String>> parserSQLSecurity(String sql) {
        Map<String, List<String>> finalRes = Maps.newHashMap();
        List<String> createRes = Lists.newArrayList();
        List<String> res = Lists.newArrayList();
        finalRes.put("other", res);
        finalRes.put("create", createRes);
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            if (statement instanceof Insert) {
                Insert insert = (Insert) statement;
                Table table = insert.getTable();
                String tableName = table.getName();
                String schemaName = table.getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
                if (Objects.nonNull(insert.getSelect())) {
                    processSelectStatement(insert.getSelect().getSelectBody(), res);
                }

            } else if (statement instanceof Select) {
                Select select = (Select) statement;
                processSelectStatement(select.getSelectBody(), res);

            } else if (statement instanceof Update) {
                Update update = (Update) statement;
                String tableName = update.getTable().getName();
                String schemaName = update.getTable().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
            } else if (statement instanceof Alter) {
                Alter alter = (Alter) statement;
                String tableName = alter.getTable().getName();
                String schemaName = alter.getTable().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
            } else if (statement instanceof Delete) {
                Delete delete = (Delete) statement;
                String tableName = delete.getTable().getName();
                String schemaName = delete.getTable().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
            } else if (statement instanceof Drop) {
                Drop drop = (Drop) statement;
                String tableName = drop.getName().getName();
                String schemaName = drop.getName().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
            } else if (statement instanceof Truncate) {
                Truncate truncate = (Truncate) statement;
                String tableName = truncate.getTable().getName();
                String schemaName = truncate.getTable().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                res.add(tableName);
            } else if (statement instanceof CreateTable) {
                CreateTable createTable = (CreateTable) statement;
                String tableName = createTable.getTable().getName();
                String schemaName = createTable.getTable().getSchemaName();
                if (StringUtils.isNotBlank(schemaName)) {
                    tableName = schemaName + "." + tableName;
                }
                createRes.add(tableName);
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        return finalRes;
    }

    @Override
    public String validSecretSQL(String sql, String userId, Long projectId) {
        Map<String, List<String>> finalRes = parserSQLSecurity(sql);
        List<String> tables = finalRes.get("other");
        List<String> create = finalRes.get("create");

        // 其他DDL操作的表只需要判断密级,创建的表需要插入用户的密级
        tblPropService.createTableSecret(create, projectId, userId);
        for (String table : tables) {
            boolean secretLevel = tblPropService.checkSecretLevel(table, projectId, userId);
            if (!secretLevel) {
                return table;
            }
        }
        return null;
    }

    private static void processSelectStatement(Select selectBody, List<String> res) {
        if (selectBody instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) selectBody;
            FromItem fromItem = plainSelect.getFromItem();
            List<Join> joins = ((PlainSelect) selectBody).getJoins();
            processJoin(joins, res);
            processFromItem(fromItem, res);
            Expression where = plainSelect.getWhere();
            processWhereClause(where, res);
        } else if (selectBody instanceof SetOperationList) {
            SetOperationList setOperationList = (SetOperationList) selectBody;
            for (Select selectBodyItem : setOperationList.getSelects()) {
                processSelectStatement(selectBodyItem, res);
            }
        }
    }

    private static void processJoin(List<Join> joins, List<String> res) {
        if (CollectionUtils.isEmpty(joins)) {
            return;
        }
        for (Join join : joins) {
            Table rightTable = (Table) join.getRightItem();
            // 获取左表和右表的表名
            String rightTableName = rightTable.getName();
            // 将表名添加到结果列表中
            res.add(rightTableName);
        }
    }

    private static void processFromItem(FromItem fromItem, List<String> res) {
        if (fromItem instanceof Table) {
            Table table = (Table) fromItem;
            String tableName = table.getName();
            String schemaName = table.getSchemaName();
            if (StringUtils.isNotBlank(schemaName)) {
                tableName = schemaName + "." + tableName;
            }
            res.add(tableName);
        } else if (fromItem instanceof LateralSubSelect) {
            LateralSubSelect subSelect = (LateralSubSelect) fromItem;
            Select selectBody = subSelect.getSelectBody();
            processSelectStatement(selectBody, res);
        }
    }

    private static void processWhereClause(Expression expression, List<String> res) {
        if (expression instanceof InExpression) {
            InExpression inExpression = (InExpression) expression;
            Expression rightExpression = inExpression.getRightExpression();
            if (rightExpression instanceof LateralSubSelect) {
                LateralSubSelect subSelect = (LateralSubSelect) rightExpression;
                Select subSelectBody = subSelect.getSelectBody();
                processSelectStatement(subSelectBody, res);
            }
        }
    }
}
